﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class AdbannerDAL : DbHelperBase
    {
        public int GetCount()
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM Adbanner WHERE 1=1 ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(int pagesize, int pageIndex)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = pagesize * pageIndex + 1;
            var end = pagesize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,Id,Title,ImgUrl,LinkUrl,TopRemark,BottomRemark,IsLock,CreateTime FROM Adbanner ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(AdbannerInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [Wei8808Cms_DB].[dbo].[Adbanner]([Id],[Title],[ImgUrl],[LinkUrl],[TopRemark],[BottomRemark],[IsLock],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@Id,@Title,@ImgUrl,@LinkUrl,@TopRemark,@BottomRemark,@IsLock,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", model.Id);
                    Db.AddInParameter(cmd, "@Title", model.Title);
                    Db.AddInParameter(cmd, "@ImgUrl", model.ImgUrl);
                    Db.AddInParameter(cmd, "@LinkUrl", model.LinkUrl);
                    Db.AddInParameter(cmd, "@TopRemark", model.TopRemark);
                    Db.AddInParameter(cmd, "@BottomRemark", model.BottomRemark);
                    Db.AddInParameter(cmd, "@IsLock", model.IsLock);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public List<AdbannerInfo> GetAdbannerList()
        {
            var strSql = new StringBuilder();
            var list = new List<AdbannerInfo>();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [Id],[Title],[ImgUrl],[LinkUrl],[TopRemark],[BottomRemark] FROM [Adbanner] where IsLock=0 ");
            strSql.Append(" ORDER by CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AdbannerInfo
                                            {
                                                Id = reader["Id"] == obj ? null : Convert.ToString(reader["Id"]),
                                                Title =
                                                    reader["Title"] == obj ? null : Convert.ToString(reader["Title"]),
                                                ImgUrl =
                                                    reader["ImgUrl"] == obj ? null : Convert.ToString(reader["ImgUrl"]),
                                                LinkUrl =
                                                    reader["LinkUrl"] == obj
                                                        ? null
                                                        : Convert.ToString(reader["LinkUrl"]),
                                                TopRemark =
                                                    reader["TopRemark"] == obj
                                                        ? null
                                                        : Convert.ToString(reader["TopRemark"]),
                                                BottomRemark =
                                                    reader["BottomRemark"] == obj
                                                        ? null
                                                        : Convert.ToString(reader["BottomRemark"])
                                            };
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool Delete(List<AdbannerInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [Adbanner] WHERE Id=@Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", DbType.String);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@Id"].Value = i.Id;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public AdbannerInfo GetAdbannerInfo(string id)
        {
            var model = new AdbannerInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [Id],[Title],[ImgUrl],[LinkUrl],[TopRemark],[BottomRemark],[IsLock] FROM [Adbanner] where Id=@Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            model.Id = reader["Id"] == obj ? null : Convert.ToString(reader["Id"]);
                            model.Title = reader["Title"] == obj ? null : Convert.ToString(reader["Title"]);
                            model.ImgUrl = reader["ImgUrl"] == obj ? null : Convert.ToString(reader["ImgUrl"]);
                            model.LinkUrl = reader["LinkUrl"] == obj ? null : Convert.ToString(reader["LinkUrl"]);
                            model.TopRemark = reader["TopRemark"] == obj ? null : Convert.ToString(reader["TopRemark"]);
                            model.BottomRemark = reader["BottomRemark"] == obj ? null : Convert.ToString(reader["BottomRemark"]);
                            if (reader["IsLock"] != DBNull.Value)
                                model.IsLock = Convert.ToInt32(reader["IsLock"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(AdbannerInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [Adbanner] SET [Title] = @Title,[ImgUrl] = @ImgUrl,[LinkUrl] = @LinkUrl,[TopRemark] = @TopRemark,[BottomRemark] = @BottomRemark,[IsLock] = @IsLock,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime WHERE [Id] = @Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Title", model.Title);
                    Db.AddInParameter(cmd, "@ImgUrl", model.ImgUrl);
                    Db.AddInParameter(cmd, "@LinkUrl", model.LinkUrl);
                    Db.AddInParameter(cmd, "@TopRemark", model.TopRemark);
                    Db.AddInParameter(cmd, "@BottomRemark", model.BottomRemark);
                    Db.AddInParameter(cmd, "@IsLock", model.IsLock);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@Id", model.Id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
